
	IF NOT EXISTS( SELECT * FROM sys.schemas WHERE name = 'platform' )
	BEGIN
		EXECUTE('CREATE SCHEMA [platform]')
	END
		-- GRANT ...
		-- DENY ...
	GO

	-- Type Sizes
	-- FieldNames	= VARCHAR(256)
	-- Token		= VARCHAR(16)

	--********************************************************
	--	Tabela de valores				
	--********************************************************
	IF OBJECT_ID('[platform].fieldValues', 'U') IS NOT NULL
		DROP TABLE [platform].fieldValues
	GO

	CREATE TABLE [platform].fieldValues
	(
		LoweredFieldName	VARCHAR(200)	NOT NULL, 
		ReversedTokenId		VARCHAR(64)		NOT NULL, 
		Value				VARBINARY(512)	NULL,	-- Valor utilizado para pesquisa
		TextValue			NVARCHAR(4000)	NULL, 
		OriginalFieldName	VARCHAR(256)	NOT NULL,
		CreatedOn			DATETIME		NOT NULL,
		UpdatedOn			DATETIME		NOT NULL,
		CONSTRAINT PK_fieldValues PRIMARY KEY (LoweredFieldName, ReversedTokenId)
	)
	GO

	-- índice criado para realizar as consultas por valores 
	CREATE INDEX IX_FieldValues_Indexes
		ON [platform].fieldValues ( LoweredFieldName, Value, ReversedTokenID ) 
	GO

	--********************************************************
	--	Tabela de relacionamento entre as entidades
	--********************************************************
	IF OBJECT_ID('[platform].dataObjectRelationships', 'U') IS NOT NULL
		DROP TABLE [platform].dataObjectRelationships
	GO

	CREATE TABLE [platform].dataObjectRelationships
	(
		ParentLoweredFieldName	VARCHAR(200)	NOT NULL,	-- Nome do campo (FIELD) que contém o relacionamento
		ParentReversedTokenId	VARCHAR(64)		NOT NULL,	-- ID da entidade (documento) que possui a outra entidade (documento)
		[Order]					INT				NOT NULL,	-- Ordem de apresentação do documento no relacionamento
		ChildLoweredName		VARCHAR(256)	NOT NULL,	-- Nome da entidade (SCHEMA)
		ChildReversedTokenId	VARCHAR(16)		NOT NULL,	-- ID da entidade
		CreatedOn				DATETIME		NOT NULL,
		UpdatedOn				DATETIME		NOT NULL,
		CONSTRAINT PK_dataObjectRelationships PRIMARY KEY (ParentLoweredFieldName, ParentReversedTokenId, [Order])
	)
	GO

	-- Indice criado para facilitar as buscas pelas dependências
	CREATE INDEX IX_dataObjectRelationships 
		ON [platform].dataObjectRelationships(ChildLoweredName, ChildReversedTokenId)
	GO


	--********************************************************
	--	Tabela de índices por **DateTime**
	--********************************************************
	--IF OBJECT_ID('[platform].fieldDateTimeIndexes', 'U') IS NOT NULL
	--  DROP TABLE [platform].fieldDateTimeIndexes
	--GO

	--CREATE TABLE [platform].fieldDateTimeIndexes
	--(
	--	LoweredFieldName	VARCHAR(256)	NOT NULL, 
	--	ReversedTokenId		VARCHAR(16)		NOT NULL, 
	--	Value				DATETIME		NULL 
	--)
	--GO

	--CREATE UNIQUE CLUSTERED INDEX IX_fieldDateTimeIndexes 
	--	ON [platform].fieldDateTimeIndexes(LoweredFieldName, Value DESC, ReversedTokenId)
	--GO


	--********************************************************
	--	Tabela de índices por **NVARCHAR(1024)**
	--********************************************************
	--IF OBJECT_ID('[platform].fieldTextIndexes', 'U') IS NOT NULL
	--  DROP TABLE [platform].fieldTextIndexes
	--GO

	--CREATE TABLE [platform].fieldTextIndexes
	--(
	--	LoweredFieldName	VARCHAR(256)	NOT NULL, 
	--	ReversedTokenId		VARCHAR(16)		NOT NULL, 
	--	Value				NVARCHAR(1024)	NULL
	--)
	--GO

	--CREATE UNIQUE CLUSTERED INDEX IX_fieldTextIndexes
	--	ON [platform].fieldTextIndexes(LoweredFieldName, Value, ReversedTokenId)
	--GO


	--********************************************************
	--	Tabela de índices por **DECIMAL(19,6)**
	--********************************************************
	--IF OBJECT_ID('[platform].fieldNumberIndexes', 'U') IS NOT NULL
	--  DROP TABLE [platform].fieldNumberIndexes
	--GO

	--CREATE TABLE [platform].fieldNumberIndexes
	--(
	--	LoweredFieldName	VARCHAR(256)	NOT NULL, 
	--	ReversedTokenId		VARCHAR(16)		NOT NULL, 
	--	Value				DECIMAL(19,6)	NULL
	--)
	--GO

	--CREATE UNIQUE CLUSTERED INDEX IX_fieldNumberIndexes
	--	ON [platform].fieldNumberIndexes(LoweredFieldName, Value, ReversedTokenId)
	--GO


	-- sp utilizada para inserção e atualização dos valores de cada campo de um documento
	-- parâmetros
	--	@ReversedTokenID	=> ID do documento
	--	@FieldAndValues		=> XML com os campos do documento e valores
	--				Formato:
	--					<doc>
	--						<fields>
	--							<f name='{field-name}' txt='{text-value}' bin='{stringBase65-binary-value}' />
	--							....
	--						</fields>
	--
	--						<refs>
	--							<r p-name='{parent-field-name}' order='' c-name='{child-schema-name}' c-token='{reversed-child-token}' />
	--						</refs>
	--					</doc>


	-- Drop stored procedure if it already exists
	IF EXISTS (
	  SELECT * 
		FROM INFORMATION_SCHEMA.ROUTINES 
	   WHERE SPECIFIC_SCHEMA = N'platform'
		 AND SPECIFIC_NAME = N'pInsertAndUpdateFieldValues' 
	)
	   DROP PROCEDURE [platform].pInsertAndUpdateFieldValues
	GO

	CREATE PROCEDURE [platform].pInsertAndUpdateFieldValues
		--@ReversedTokenId	VARCHAR( 16 ), 
		@FieldAndValues		XML 
	AS
		SET NOCOUNT ON;
		--SET QUERY_GOVERNOR_COST_LIMIT 30;	-- 30 segundos para executar
	
		--------------------------
		-- Atualização dos campos
		MERGE INTO [platform].fieldValues original
		USING (SELECT	data.content.query('.') AS FieldXml
						, data.content.value('./@t', 'varchar(64)')					AS ReversedTokenId
						, data.content.value('./@original', 'varchar(256)' )		AS OriginalFieldName
						, LOWER( data.content.value('./@name', 'varchar(200)') )	AS LoweredFieldName
						, data.content.value('./@txt', 'nvarchar(4000)')			AS FieldValue
						, data.content.value('./@bin', 'varbinary(512)')			AS BinValue
				FROM @FieldAndValues.nodes('/doc/fields/f') AS data(content) ) AS sourceData

			-- Critério para atualizar os campos do documento
			ON (original.LoweredFieldName = sourceData.LoweredFieldName
				AND original.ReversedTokenId = sourceData.ReversedTokenId)
				
			-- Atualiza somente se houve mudança
		WHEN MATCHED AND original.Value <> sourceData.BinValue THEN
			UPDATE SET Value = sourceData.BinValue, TextValue = sourceData.FieldValue, UpdatedOn = GETDATE()

			-- Adiciona
		WHEN NOT MATCHED THEN
			INSERT (LoweredFieldName, OriginalFieldName, ReversedTokenId, Value, TextValue, CreatedOn, UpdatedOn)
				VALUES (sourceData.LoweredFieldName, sourceData.OriginalFieldName, sourceData.ReversedTokenId, sourceData.BinValue, sourceData.FieldValue, GETDATE(), GETDATE())

			-- Exclui
		WHEN NOT MATCHED BY SOURCE THEN
			DELETE;


		-----------------------------------
		-- Atualização dos relacionamentos
		MERGE INTO [platform].dataObjectRelationships AS original
		USING (SELECT	
						data.relations.query('.')	AS RelationXml
						, data.relations.value( './@p-token', 'varchar(64)' )			AS ParentReversedTokenId
						, LOWER( data.relations.value( './@p-name', 'varchar(200)' ) )	AS ParentLoweredFieldName
						, data.relations.value( './@order', 'int' )						AS RelationOrder
						, LOWER( data.relations.value( './@c-name', 'varchar(256)' ) )	AS ChildLoweredName
						, data.relations.value( './@c-token', 'varchar(16)' )			AS ChildReversedTokenId
				FROM @FieldAndValues.nodes('/doc/refs/r') AS data(relations) ) AS sourceData
			ON original.ParentLoweredFieldName = sourceData.ParentLoweredFieldName
				AND original.ParentReversedTokenId = sourceData.ParentReversedTokenId
				AND original.[Order] = sourceData.RelationOrder

			-- Atualização do relationamento
		WHEN MATCHED THEN
			UPDATE SET 
				ChildLoweredName = sourceData.ChildLoweredName
				, ChildReversedTokenId = sourceData.ChildReversedTokenId
				, UpdatedOn = GETDATE()
		
			-- Adiciona o relacionamento
		WHEN NOT MATCHED THEN
			INSERT ( ParentLoweredFieldName, ParentReversedTokenId, [Order], ChildLoweredName, ChildReversedTokenId, CreatedOn, UpdatedOn )
				VALUES ( sourceData.ParentLoweredFieldName, sourceData.ParentReversedTokenId, sourceData.RelationOrder, sourceData.ChildLoweredName, sourceData.ChildReversedTokenId, GETDATE(), GETDATE() )

			-- Não precisa mais do relacionamento
		WHEN NOT MATCHED BY SOURCE THEN
			DELETE;
	GO


	-- =============================================
	-- SP de seleção dos dados dos documentos filhos
	-- relacionados a um documento pai
	-- =============================================

	-- Drop stored procedure if it already exists
	IF EXISTS (
	  SELECT * 
		FROM INFORMATION_SCHEMA.ROUTINES 
	   WHERE SPECIFIC_SCHEMA = N'platform'
		 AND SPECIFIC_NAME = N'pSelectDocumentChildren' 
	)
	   DROP PROCEDURE [platform].pSelectDocumentChildren
	GO

	CREATE PROCEDURE [platform].pSelectDocumentChildren
		@LoweredSchemaName	VARCHAR(200), 
		@ReversedTokenId	VARCHAR(64)
	AS
	
		SELECT	children.ParentLoweredFieldName,
				fields.* 
		FROM [platform].fieldValues fields
			INNER JOIN [platform].dataObjectRelationships children 
				ON children.ChildLoweredName LIKE fields.LoweredFieldName + '%'
					AND children.ChildReversedTokenId = fields.ReversedTokenId
		WHERE children.ParentLoweredFieldName LIKE @LoweredSchemaName + '%'
		  AND children.ParentReversedTokenId = @ReversedTokenId	
		ORDER BY children.ParentLoweredFieldName, children.ParentReversedTokenId, children.[Order]
	GO

	-- 
	--IF object_id(N'platform.vFieldValues', 'V') IS NOT NULL
	--	DROP VIEW platform.vFieldValues
	--GO

	--CREATE VIEW platform.vFieldValues AS
	--BEGIN
	--	SELECT	LoweredFieldName
	--			, ReversedTokenId
	--			, Value	
	--			, TextValue
	--			, CreatedOn	
	--			, UpdatedOn
	--	FROM [plaform].fieldValues 
	--	--ORDER BY TotalDue DESC
	--END

	--GO

-- Como criar a stored procedure
	
--declare @x as xml


--select @x = CAST( '<doc><fields><f name=''sku'' txt=''123-p'' bin=''''/><f name=''price'' txt=''10.34'' bin=''''/></fields></doc>' as xml); 



--select data.content.query('.'),
--		data.content.value('./@name', 'varchar(256)'),
--		data.content.value('./@txt', 'nvarchar(4000)'),
--		data.content.value('./@bin', 'varbinary(512)')
--from @x.nodes('/doc/fields/f') as data(content)


--;with test(blob, notblob) as (select 0x776962626C65, 'fabiano')

--select blob, notblob from test
--  for xml raw, binary base64


-- FUNCIONA
--declare @x as xml

--select @x = CAST( '<doc><fields><f name="plaform.objectmodel.schema[friendlyname]" txt="Marca" bin="TWFyY2E=" /></fields></doc>' as xml); 
--select data.content.query('.'),
--		data.content.value('./@name', 'varchar(256)'),
--		data.content.value('./@txt', 'nvarchar(4000)'),
--		CAST( data.content.value('./@bin', 'varbinary(512)') AS VARCHAR(4000))
--from @x.nodes('/doc/fields/f') as data(content)